package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import sgc.struc.Suplier;

@ManagedBean(name = "suplierBean")
@RequestScoped
public class SuplierBean implements Serializable {

	private static final long serialVersionUID = 3960836829028613020L;
	private Suplier suplier;
	private DataSource ds;
	private HtmlDataTable htmlDataTable;
	private int tabindex;

	public SuplierBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		suplier = new Suplier();
	}

	public Suplier getsuplier() {
		return suplier;
	}

	public HtmlDataTable getHtmlDataTable() {
		return htmlDataTable;
	}

	public int getTabindex() {
		return tabindex;
	}

	public void setSuplier(Suplier suplier) {
		this.suplier = suplier;
	}

	public void setHtmlDataTable(HtmlDataTable htmlDataTable) {
		this.htmlDataTable = htmlDataTable;
	}

	public void setTabindex(int tabindex) {
		this.tabindex = tabindex;
	}

	private int act_company() {
		int company;
		company = 0;
		try {
			FacesContext context = FacesContext.getCurrentInstance();
			CheckUserBean cu = (CheckUserBean) context.getApplication()
					.evaluateExpressionGet(context, "#{checkUserBean}",
							CheckUserBean.class);
			company = cu.getUsuario().getCompany_code();
		} catch (Exception e) {
		}
		return company;

	}

	public List<Suplier> getSuplierList() throws SQLException {

		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");
		String simpleProc = "{ CALL sp_GetAllsuplier_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());

		cs.execute();
		ResultSet result = cs.getResultSet();
		
		List<Suplier> list = new ArrayList<Suplier>();

		while (result.next()) {
			Suplier aux_suplier = new Suplier();
			aux_suplier.setCompany(result.getString(2));
			aux_suplier.setCode(result.getInt(3));
			aux_suplier.setName1(result.getString(4));
			aux_suplier.setName2(result.getString(5));
			aux_suplier.setName3(result.getString(6));
			aux_suplier.setName4(result.getString(7));
			aux_suplier.setAddress_st(result.getString(8));
			aux_suplier.setAddress_no(result.getString(9));
			aux_suplier.setAddress_fl(result.getString(10));
			aux_suplier.setAddress_ap(result.getString(11));
			aux_suplier.setCity(result.getString(12));
			aux_suplier.setState(result.getString(13));
			aux_suplier.setCountry(result.getString(14));
			aux_suplier.setPostal_code(result.getString(15));
			aux_suplier.setTax_id_1(result.getString(16));
			aux_suplier.setTax_type_1(result.getString(17));
			aux_suplier.setTax_id_2(result.getString(18));
			aux_suplier.setTax_type_2(result.getString(19));
			aux_suplier.setStart_date(result.getDate(20));
			aux_suplier.setPhone(result.getString(21));
			aux_suplier.setMail(result.getString(22));
		
			list.add(aux_suplier);
		}

		con.close();

		return list;
	}

	public List<String> getNameList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllsuplier_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();
		String name;
		while (result.next()) {
			name = result.getInt(3) + " - " + result.getString(6) + " " + result.getString(7) + ", " + result.getString(4) + " " + result.getString(5);
			list.add(name);
		}
		con.close();
		return list;
	}

	public String edit() {
		suplier = (Suplier) htmlDataTable.getRowData();
		tabindex = 0;
		return "OK";
	}

	public boolean check(int p_code) throws SQLException {
		int aux_cod;
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_Getsuplier(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, p_code);
		cs.setInt(2, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		aux_cod = result.getInt(1);
		con.close();

		if (aux_cod != 0) {
			return true;
		} else {
			return false;
		}
	}
	private java.sql.Date convertDate(java.util.Date jDate) {
		java.sql.Date sqlDate;
		long t;

		if (jDate == null) {
			sqlDate = new java.sql.Date(0);
		} else {
			t = jDate.getTime();
			sqlDate = new java.sql.Date(t);
		}
		return sqlDate;
	}
	
	public void save() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc;
		if (check(suplier.getCode()) == true) {
			simpleProc = "{ CALL sp_updsuplier(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
		} else {
			simpleProc = "{ CALL sp_addsuplier(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
		}

		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1,act_company());
		cs.setInt(2, suplier.getCode());
        cs.setString(3,suplier.getName1());
        cs.setString(4,suplier.getName2());
        cs.setString(5,suplier.getName3());
        cs.setString(6,suplier.getName4());
        cs.setString(7,suplier.getAddress_st());
        cs.setString(8,suplier.getAddress_no());
        cs.setString(9,suplier.getAddress_fl());
        cs.setString(10,suplier.getAddress_ap());
        cs.setString(11,suplier.getCity());
        cs.setString(12,suplier.getState());
        cs.setString(13,suplier.getCountry());
        cs.setString(14,suplier.getPostal_code());
        cs.setString(15,suplier.getTax_id_1());
        cs.setString(16,suplier.getTax_type_1());
        cs.setString(17,suplier.getTax_id_2());
        cs.setString(18,suplier.getTax_type_2());
        cs.setDate(19,convertDate(suplier.getStart_date()));
        cs.setString(20,suplier.getPhone());
        cs.setString(21,suplier.getMail());
        
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		String aux_message = result.getString(3);
		FacesContext.getCurrentInstance()
				.addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));
		suplier = null;

	}

}
